package top.psjj.news.dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import top.psjj.news.po.TNews;
import top.psjj.news.utils.DataSourceUtils;

import java.sql.Connection;
import java.util.List;

/**
 * @Auther:小陈的Java
 */
public class NewsDao {
    private QueryRunner queryRunner = new QueryRunner();
    public void add(TNews news) throws Exception {
        String sql = "insert into t_news(title,author,type_id,ishead,ishot,isimg,content,public_date,click,img_url) values(?,?,?,?,?,?,?,?,?,?)";
        queryRunner.update(DataSourceUtils.getConnection(),sql,news.getTitle(),news.getAuthor(),
                news.getTypeId(),news.getIshead(),news.getIshot(),news.getIsimg(),news.getContent(),
                news.getPublicDate(),news.getClick(),news.getImgUrl());
    }

    public List<TNews> queryAll(String sql)throws Exception {
       /* List<TNews> result = queryRunner.query(DataSourceUtils.getConnection(),
                "select id,title,author,type_id typeId,isHead ishead,isImg isimg,isHot ishot,content,public_date publicDate,click,img_url imgUrl from" +
                        " t_news order by public_date desc"
                ,new BeanListHandler<TNews>(TNews.class));*/
        List<TNews> result = queryRunner.query(DataSourceUtils.getConnection(),sql,new BeanListHandler<TNews>(TNews.class));
        return result;
    }

    public void del(int id) throws Exception{
        String sql = "delete  from t_news where id =?";
        queryRunner.update(DataSourceUtils.getConnection(),sql,id);
    }

    public TNews queryTNewsById(int id) throws Exception{
        Connection connection = DataSourceUtils.getConnection();
        String sql = "select id,title,author,type_id typeId,isHead ishead,isImg isimg,isHot ishot,content,public_date publicDate,click,img_url imgUrl from t_news  where id=?";
        BeanHandler<TNews> handler = new BeanHandler<>(TNews.class);
        TNews result = queryRunner.query(connection, sql,handler,id);
        return result;
    }

    public void update(TNews tNews) throws Exception{
        String sql = "update t_news set title=?,author=?,type_id typeId=?,isHead ishead=?,isImg isimg=?," +
                "isHot ishot=?,content=?,public_date publicDate=?,click=?,img_url imgUrl=? where id=?";
        queryRunner.update(DataSourceUtils.getConnection(),
                sql,tNews.getTitle(),tNews.getAuthor(),tNews.gettType(),tNews.getIshead(),
                tNews.getIsimg(),tNews.getIshot(),tNews.getContent(),tNews.getPublicDate(),
                tNews.getClick(),tNews.getImgUrl(),tNews.getId());

    }

    public TNews queryNewsById(Integer newsId) throws Exception{
        Connection connection = DataSourceUtils.getConnection();
        String sql = "select id,title,author,type_id typeId,isHead ishead,isImg isimg,isHot ishot,content,public_date publicDate,click,img_url imgUrl from t_news  where id=?";
        BeanHandler<TNews> handler = new BeanHandler<>(TNews.class);
        TNews result = queryRunner.query(connection, sql,handler,newsId);
        return result;
    }

    public List<TNews> getImgList() throws Exception {
        List<TNews> result = queryRunner.query(DataSourceUtils.getConnection(),
                "select id,title,author,type_id typeId,isHead ishead,isImg isimg,isHot ishot,content,public_date publicDate,click,img_url imgUrl from" +
                        " t_news where isImg=1  order by public_date desc"
                ,new BeanListHandler<TNews>(TNews.class));
        return result;
    }

    public TNews getHeader() throws Exception {
        String sql = "select id,title,author,type_id typeId,isHead ishead,isImg isimg,isHot ishot,content,public_date publicDate,click,img_url imgUrl from " +
                " t_news where isHead=1   order by public_date desc limit 0,1 ";
        TNews news = queryRunner.query(DataSourceUtils.getConnection(),sql,new BeanHandler<TNews>(TNews.class));
        return news;
    }

    public List<TNews> getUpdateNews() throws Exception {
        String sql = "select id,title,author,type_id typeId,isHead ishead,isImg isimg,isHot ishot,content,public_date publicDate,click,img_url imgUrl from " +
                " t_news order by public_date desc limit 0,5";
        List<TNews> result = queryRunner.query(DataSourceUtils.getConnection(),sql,new BeanListHandler<TNews>(TNews.class));
        return  result;
    }

    public List<TNews> getHotNews() throws Exception {
        String sql = "select id,title,author,type_id typeId,isHead ishead,isImg isimg,isHot ishot,content,public_date publicDate,click,img_url imgUrl from " +
                " t_news where isHot=1 order by public_date desc limit 0,5";
        List<TNews> result = queryRunner.query(DataSourceUtils.getConnection(),sql,new BeanListHandler<TNews>(TNews.class));
        return  result;
    }

    public List<TNews> queryNewsBytypeId(Integer typeId) throws Exception {
        String sql = "select id,title,author,type_id typeId,isHead ishead,isImg isimg,isHot ishot,content,public_date publicDate,click,img_url imgUrl from " +
                " t_news where type_id=? order by public_date desc limit 0,8";
        List<TNews> result = queryRunner.query(DataSourceUtils.getConnection(),sql,new BeanListHandler<TNews>(TNews.class),typeId);
        return  result;
    }
}
